GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_ShippingMethods]') AND type in (N'U'))
BEGIN
CREATE TABLE [xz_ShippingMethods](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[ShippingMethodId] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_xz_ShippingMethods_ShippingMethodId]  DEFAULT (newid()),
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Price] [money] NOT NULL,
 CONSTRAINT [PK_xz_ShippingMethods] PRIMARY KEY CLUSTERED 
(
	[ShippingMethodID] ASC
)
) ON [PRIMARY]
END
GO
ALTER TABLE [xz_ShippingMethods]  WITH CHECK ADD  CONSTRAINT [FK_xz_ShippingMethods_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [aspnet_Applications] ([ApplicationId])
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_OrderStatuses]') AND type in (N'U'))
BEGIN
CREATE TABLE [xz_OrderStatuses](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[OrderStatusId] [int] IDENTITY(1,1) NOT NULL,
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_xz_OrderStatuses] PRIMARY KEY CLUSTERED 
(
	[OrderStatusId] ASC
)
) ON [PRIMARY]
END
GO
ALTER TABLE [xz_OrderStatuses]  WITH CHECK ADD  CONSTRAINT [FK_xz_OrderStatuses_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [aspnet_Applications] ([ApplicationId])
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Departments]') AND type in (N'U'))
BEGIN
CREATE TABLE [xz_Categories](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[CategoryId] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_xz_Categories_CategoryId]  DEFAULT (newid()),
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Importance] [int] NOT NULL CONSTRAINT [DF_xz_Categories_Importance]  DEFAULT ((0)),
	[Description] [nvarchar](4000) NULL,
	[ImageUrl] [nvarchar](256) NULL,
 CONSTRAINT [PK_xz_Categories] PRIMARY KEY CLUSTERED 
(
	[CategoryId] ASC
)
) ON [PRIMARY]
END
GO
ALTER TABLE [xz_Categories]  WITH CHECK ADD  CONSTRAINT [FK_xz_Categories_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [aspnet_Applications] ([ApplicationId])
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Orders]') AND type in (N'U'))
BEGIN
CREATE TABLE [xz_Orders](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[OrderId] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_xz_Orders_OrderId]  DEFAULT (newid()),
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) NOT NULL,
	[StatusId] [int] NOT NULL,
	[ShippingMethod] [nvarchar](256) NOT NULL,
	[SubTotal] [money] NOT NULL,
	[Shipping] [money] NOT NULL,
	[ShippingFirstName] [nvarchar](256) NOT NULL,
	[ShippingLastName] [nvarchar](256) NOT NULL,
	[ShippingStreet] [nvarchar](256) NOT NULL,
	[ShippingPostalCode] [nvarchar](50) NOT NULL,
	[ShippingCity] [nvarchar](256) NOT NULL,
	[ShippingState] [nvarchar](256) NOT NULL,
	[ShippingCountry] [nvarchar](256) NOT NULL,
	[CustomerEmail] [nvarchar](256) NOT NULL,
	[CustomerPhone] [nvarchar](50) NOT NULL,
	[CustomerFax] [nvarchar](50) NOT NULL,
	[ShippedDate] [datetime] NULL,
	[TransactionId] [nvarchar](256) NULL,
	[TrackingId] [nvarchar](256) NULL,
 CONSTRAINT [PK_xz_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)
) ON [PRIMARY]
END
GO
ALTER TABLE [xz_Orders]  WITH CHECK ADD  CONSTRAINT [FK_xz_Orders_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [aspnet_Applications] ([ApplicationId])
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_Products]') AND type in (N'U'))
BEGIN
CREATE TABLE [xz_Products](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[ProductId] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_xz_Products_ProductId]  DEFAULT (newid()),
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) NOT NULL,
	[CategoryId] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[Description] [ntext] NOT NULL,
	[SKU] [nvarchar](50) NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[DiscountPercentage] [int] NOT NULL CONSTRAINT [DF_xz_Products_DiscountPercentage]  DEFAULT ((0)),
	[UnitsInStock] [int] NOT NULL,
	[SmallImageUrl] [nvarchar](256) NULL,
	[FullImageUrl] [nvarchar](256) NULL,
	[Votes] [int] NOT NULL CONSTRAINT [DF_xz_Products_Votes]  DEFAULT ((0)),
	[TotalRating] [int] NOT NULL CONSTRAINT [DF_xz_Products_TotalRating]  DEFAULT ((0)),
 CONSTRAINT [PK_xz_Products] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
ALTER TABLE [xz_Products]  WITH CHECK ADD  CONSTRAINT [FK_xz_Products_aspnet_Applications] FOREIGN KEY([ApplicationId])
REFERENCES [aspnet_Applications] ([ApplicationId])
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_OrderItems]') AND type in (N'U'))
BEGIN
CREATE TABLE [xz_OrderItems](
	[OrderItemId] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_xz_OrderItems_OrderItemId]  DEFAULT (newid()),
	[AddedDate] [datetime] NOT NULL,
	[AddedBy] [nvarchar](256) NOT NULL,
	[OrderID] [uniqueidentifier] NOT NULL,
	[ProductID] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](256) NOT NULL,
	[SKU] [nvarchar](256) NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[Quantity] [int] NOT NULL,
 CONSTRAINT [PK_xz_OrderItems] PRIMARY KEY CLUSTERED 
(
	[OrderItemID] ASC
)
) ON [PRIMARY]
END
GO
ALTER TABLE [xz_OrderItems]  WITH CHECK ADD  CONSTRAINT [FK_xz_OrderItems_xz_Orders] FOREIGN KEY([OrderId])
REFERENCES [xz_Orders] ([OrderId])
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_DeleteShippingMethod]') AND type in (N'P', N'PC'))
BEGIN
EXEC sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_DeleteShippingMethod]
(
    @ApplicationName	nvarchar(256),
	@ShippingMethodId	uniqueidentifier
)
AS
BEGIN
    SELECT  @ShippingMethodId = s.ShippingMethodId
    FROM    xz_ShippingMethods s, aspnet_Applications a
    WHERE   s.ShippingMethodId = @ShippingMethodId
        AND s.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@ShippingMethodId IS NULL)
        RETURN 0

	DELETE FROM xz_ShippingMethods WHERE ShippingMethodId = @ShippingMethodId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetShippingMethods]') AND type in (N'P', N'PC'))
BEGIN
EXEC sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetShippingMethods]
(
    @ApplicationName	nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0
	
	SET NOCOUNT ON

	SELECT ShippingMethodId, AddedDate, AddedBy, Title, Price
		FROM xz_ShippingMethods 
		WHERE ApplicationId = @ApplicationId
		ORDER BY Price ASC
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetShippingMethodByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetShippingMethodById]
(
    @ApplicationName	nvarchar(256),
	@ShippingMethodId	uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0
	
	SET NOCOUNT ON

	SELECT ShippingMethodId, AddedDate, AddedBy, Title, Price
	   FROM xz_ShippingMethods
	   WHERE ShippingMethodId = @ShippingMethodId
	AND ApplicationId = @ApplicationId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_InsertShippingMethod]') AND type in (N'P', N'PC'))
BEGIN
EXEC sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_InsertShippingMethod]
(
    @ApplicationName	nvarchar(256),
    @AddedDate			datetime,
    @AddedBy			nvarchar(256),
    @Title				nvarchar(256),
    @Price				money,
    @ShippingMethodId   uniqueidentifier OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	SET NOCOUNT ON

	-- check whether a ShippingMethod with the same name already exists
	DECLARE @CurrId uniqueidentifier
	SELECT @CurrId = ShippingMethodId FROM xz_ShippingMethods WHERE
	   LOWER(@Title) = LOWER(Title) AND ApplicationId = @ApplicationId

	IF @CurrId IS NOT NULL
		BEGIN
		--SET @ShippingMethodID = -1
			RETURN -1
		END

    IF( @ShippingMethodId IS NULL )
        SELECT @ShippingMethodId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT ShippingMethodId FROM xz_ShippingMethods
                    WHERE ShippingMethodId = @ShippingMethodId ) )
            RETURN -1
    END
	   
	INSERT INTO xz_ShippingMethods 
	   (ApplicationId, AddedDate, AddedBy, Title, Price)
	   VALUES (@ApplicationId, @AddedDate, @AddedBy, @Title, @Price)

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_UpdateShippingMethod]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_UpdateShippingMethod]
(
    @ApplicationName	nvarchar(256),
	@ShippingMethodId   uniqueidentifier,
	@Title				nvarchar(256),
	@Price				money
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_ShippingMethods
	   SET Title = @Title, Price = @Price
	   WHERE ShippingMethodId = @ShippingMethodId	
	   AND ApplicationId = @ApplicationId

END' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_UpdateOrderStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_UpdateOrderStatus]
(
    @ApplicationName	nvarchar(256),
	@OrderStatusID		int,
	@Title				nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_OrderStatuses
	   SET Title = @Title
	   WHERE OrderStatusID = @OrderStatusID	
	   AND ApplicationId = @ApplicationId

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetOrderStatusByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetOrderStatusById]
(
    @ApplicationName	nvarchar(256),
	@OrderStatusId		int
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT OrderStatusID, AddedDate, AddedBy, Title
		FROM xz_OrderStatuses
		WHERE OrderStatusId = @OrderStatusId
		AND ApplicationId = @ApplicationId
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_InsertOrderStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_InsertOrderStatus]
(
    @ApplicationName	nvarchar(256),
	@AddedDate			datetime,
	@AddedBy			nvarchar(256),
	@Title				nvarchar(256),
	@OrderStatusId		int OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	SET NOCOUNT ON

	-- check whether a OrderStatus with the same name already exists
	DECLARE @CurrId int
	SELECT @CurrId = OrderStatusID FROM xz_OrderStatuses WHERE
	   LOWER(@Title) = LOWER(Title) AND ApplicationId = @ApplicationId

	IF @CurrId IS NOT NULL
		BEGIN
			SET @OrderStatusId = -1
			RETURN
		END
	   
	INSERT INTO xz_OrderStatuses 
		(ApplicationId, AddedDate, AddedBy, Title)
		VALUES (@ApplicationId, @AddedDate, @AddedBy, @Title)

	SET @OrderStatusID = scope_identity()
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetOrderStatuses]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetOrderStatuses]
(
    @ApplicationName	nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0
	
	SET NOCOUNT ON

	SELECT OrderStatusID, AddedDate, AddedBy, Title
		FROM xz_OrderStatuses
		WHERE ApplicationId = @ApplicationId
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_DeleteOrderStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_DeleteOrderStatus]
(
    @ApplicationName	nvarchar(256),
	@OrderStatusId		int
)
AS
BEGIN
    SELECT  @OrderStatusId = o.OrderStatusId
    FROM    [xz_OrderStatuses] o, aspnet_Applications a
    WHERE   o.OrderStatusId = @OrderStatusId
        AND o.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@OrderStatusId IS NULL)
        RETURN 0

	DELETE FROM xz_OrderStatuses WHERE OrderStatusId = @OrderStatusId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetOrderById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetOrderById]
(
    @ApplicationName	nvarchar(256),
	@OrderId			uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON
	   
	SELECT xz_Orders.OrderId, xz_Orders.AddedDate, xz_Orders.AddedBy, xz_Orders.StatusId, xz_Orders.ShippingMethod, xz_Orders.SubTotal, 
		  xz_Orders.Shipping, xz_Orders.ShippingFirstName, xz_Orders.ShippingLastName, xz_Orders.ShippingStreet, xz_Orders.ShippingPostalCode, 
		  xz_Orders.ShippingCity, xz_Orders.ShippingState, xz_Orders.ShippingCountry, xz_Orders.CustomerEmail, xz_Orders.CustomerPhone, 
		  xz_Orders.CustomerFax, xz_Orders.ShippedDate, xz_Orders.TransactionId, xz_Orders.TrackingId,
		  xz_OrderStatuses.Title AS StatusTitle
	   FROM xz_Orders INNER JOIN
		  xz_OrderStatuses ON xz_Orders.StatusId = xz_OrderStatuses.OrderStatusId
	   WHERE xz_Orders.OrderId = @OrderId    
		AND xz_Orders.ApplicationId = @ApplicationId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetOrdersByStatus]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetOrdersByStatus]
(
    @ApplicationName	nvarchar(256),
	@StatusId			int,
	@FromDate			datetime,
	@ToDate				datetime
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON
   
	SELECT xz_Orders.OrderID, xz_Orders.AddedDate, xz_Orders.AddedBy, xz_Orders.StatusID, xz_Orders.ShippingMethod, xz_Orders.SubTotal, 
			xz_Orders.Shipping, xz_Orders.ShippingFirstName, xz_Orders.ShippingLastName, xz_Orders.ShippingStreet, xz_Orders.ShippingPostalCode, 
			xz_Orders.ShippingCity, xz_Orders.ShippingState, xz_Orders.ShippingCountry, xz_Orders.CustomerEmail, xz_Orders.CustomerPhone, 
			xz_Orders.CustomerFax, xz_Orders.ShippedDate, xz_Orders.TransactionID, xz_Orders.TrackingID,
			xz_OrderStatuses.Title AS StatusTitle
		FROM xz_Orders INNER JOIN
			xz_OrderStatuses ON xz_Orders.StatusID = xz_OrderStatuses.OrderStatusID
		WHERE xz_Orders.StatusID = @StatusID AND xz_Orders.AddedDate >= @FromDate AND xz_Orders.AddedDate <= @ToDate
		AND xz_Orders.ApplicationId = @ApplicationId
		ORDER BY xz_Orders.AddedDate ASC
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetOrdersByCustomer]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[xz_WebShop_GetOrdersByCustomer]
(
    @ApplicationName	nvarchar(256),
	@AddedBy    nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON
   
	SELECT xz_Orders.OrderId, xz_Orders.AddedDate, xz_Orders.AddedBy, xz_Orders.StatusId, xz_Orders.ShippingMethod, xz_Orders.SubTotal, 
			xz_Orders.Shipping, xz_Orders.ShippingFirstName, xz_Orders.ShippingLastName, xz_Orders.ShippingStreet, xz_Orders.ShippingPostalCode, 
			xz_Orders.ShippingCity, xz_Orders.ShippingState, xz_Orders.ShippingCountry, xz_Orders.CustomerEmail, xz_Orders.CustomerPhone, 
			xz_Orders.CustomerFax, xz_Orders.ShippedDate, xz_Orders.TransactionId, xz_Orders.TrackingId,
			xz_OrderStatuses.Title AS StatusTitle
		FROM xz_Orders INNER JOIN
			xz_OrderStatuses ON xz_Orders.StatusId = xz_OrderStatuses.OrderStatusId
		WHERE xz_Orders.AddedBy = @AddedBy
		AND xz_Orders.ApplicationId = @ApplicationId
		ORDER BY xz_Orders.AddedDate DESC
END' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_UpdateCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_UpdateCategory]
(
    @ApplicationName	nvarchar(256),
	@CategoryId			uniqueidentifier,
	@Title				nvarchar(256),
	@Importance			int,
	@Description		nvarchar(4000),
	@ImageUrl			nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId UNIQUEIDENTIFIER
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_Categories
	   SET Title = @Title, Importance = @Importance, Description = @Description, ImageUrl = @ImageUrl
	   WHERE CategoryId = @CategoryId	
	   AND ApplicationId = @ApplicationId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetProductById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetProductById]
(
    @ApplicationName	nvarchar(256),
	@ProductId			uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT xz_Products.ProductId, xz_Products.AddedDate, xz_Products.AddedBy, xz_Products.CategoryId, xz_Products.Title, xz_Products.Description, xz_Products.SKU, xz_Products.UnitPrice,
			xz_Products.DiscountPercentage, xz_Products.UnitsInStock, xz_Products.SmallImageUrl, xz_Products.FullImageUrl, xz_Products.Votes, xz_Products.TotalRating, 
			xz_Categories.Title AS CategoryTitle
		FROM xz_Products INNER JOIN
			xz_Categories ON xz_Products.CategoryId = xz_Categories.CategoryId
		WHERE ProductId = @ProductId
		AND xz_Products.ApplicationId = @ApplicationId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_InsertCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_InsertCategory]
(
    @ApplicationName	nvarchar(256),
	@AddedDate     datetime,
	@AddedBy       nvarchar(256),
	@Title         nvarchar(256),
	@Importance    int,
	@Description   nvarchar(4000),
	@ImageUrl      nvarchar(256),
	@CategoryId    uniqueidentifier OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	SET NOCOUNT ON

	-- check whether a Department with the same name already exists
	DECLARE @CurrId uniqueidentifier
	SELECT @CurrId = CategoryId FROM xz_Categories WHERE
	   LOWER(@Title) = LOWER(Title)

	IF @CurrId IS NOT NULL
		BEGIN
			--SET @CategoryId = -1
			RETURN
		END
	   
    IF( @CategoryId IS NULL )
        SELECT @CategoryId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT CategoryId FROM xz_Categories
                    WHERE CategoryId = @CategoryId ) )
            RETURN -1
    END

	INSERT INTO xz_Categories 
		(ApplicationId, AddedDate, AddedBy, CategoryId, Title, Importance, Description, ImageUrl)
		VALUES (@ApplicationId, @AddedDate, @AddedBy, @CategoryId, @Title, @Importance, @Description, @ImageUrl)
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_DeleteCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_DeleteCategory]
(
    @ApplicationName	nvarchar(256),
	@CategoryId			uniqueidentifier
)
AS
BEGIN
    SELECT  @CategoryId = c.CategoryId
    FROM    [xz_Categories] c, aspnet_Applications a
    WHERE   c.CategoryId = @CategoryId
        AND c.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@CategoryId IS NULL)
        RETURN 0

	DELETE FROM [xz_Categories] WHERE CategoryId = @CategoryId
	
	RETURN 0
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetCategories]
(
    @ApplicationName	nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT CategoryId, AddedDate, AddedBy, Title, Importance, Description, ImageUrl
		FROM dbo.xz_Categories
		WHERE [ApplicationId] = @ApplicationId
		ORDER BY Importance DESC, Title ASC

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetCategoryById]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetCategoryById]
(
    @ApplicationName	nvarchar(256),
	@CategoryId			uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT CategoryId, AddedDate, AddedBy, Title, Importance, Description, ImageUrl
		FROM xz_Categories
		WHERE CategoryId = @CategoryId
		AND [ApplicationId] = @ApplicationId

END' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_DeleteProduct]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_DeleteProduct]
(
    @ApplicationName	nvarchar(256),
	@ProductId			uniqueidentifier
)
AS
BEGIN
    SELECT  @ProductId = p.ProductId
    FROM    xz_Products p, aspnet_Applications a
    WHERE   p.ProductId = @ProductId
        AND p.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@ProductId IS NULL)
        RETURN 0

	DELETE FROM xz_Products WHERE ProductId = @ProductId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetProductDescription]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetProductDescription]
(
    @ApplicationName	nvarchar(256),
	@ProductId			uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0
	
	SET NOCOUNT ON

	SELECT Description FROM xz_Products WHERE ProductId = @ProductId AND ApplicationId = @ApplicationId

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetProductCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetProductCount]
(
    @ApplicationName	nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT COUNT(*) FROM xz_Products WHERE ApplicationId = @ApplicationId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_InsertProduct]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[xz_WebShop_InsertProduct]
(
    @ApplicationName	nvarchar(256),
	@AddedDate           datetime,
	@AddedBy             nvarchar(256),
	@CategoryId          uniqueidentifier,
	@Title               nvarchar(256),
	@Description         ntext,
	@SKU                 nvarchar(50),
	@UnitPrice           money,
	@DiscountPercentage  int,
	@UnitsInStock        int,
	@SmallImageUrl       nvarchar(256),
	@FullImageUrl        nvarchar(256),
	@ProductId           uniqueidentifier OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	SET NOCOUNT ON

    IF( @ProductId IS NULL )
        SELECT @ProductId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT ProductId FROM xz_Products
                    WHERE ProductId = @ProductId ) )
            RETURN -1
    END

	SET NOCOUNT ON
	   
	INSERT INTO xz_Products 
	   (ApplicationId, AddedDate, AddedBy, CategoryId, Title, Description, SKU, UnitPrice, DiscountPercentage, UnitsInStock, SmallImageUrl, FullImageUrl)
	   VALUES (@ApplicationId, @AddedDate, @AddedBy, @CategoryId, @Title, @Description, @SKU, @UnitPrice, @DiscountPercentage, @UnitsInStock, @SmallImageUrl, @FullImageUrl)

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_InsertVote]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_InsertVote]
(
    @ApplicationName	nvarchar(256),
    @ProductId			uniqueidentifier,
    @Rating				int
)
AS
BEGIN 
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN -1

	UPDATE xz_Products 
	   SET Votes = Votes + 1, TotalRating = TotalRating + @Rating
	   WHERE ProductId = @ProductId AND ApplicationId = @ApplicationId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetProductCountByCategory]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetProductCountByCategory]
(
    @ApplicationName	nvarchar(256),
	@CategoryId			uniqueidentifier
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	SET NOCOUNT ON

	SELECT COUNT(*) FROM xz_Products WHERE CategoryId = @CategoryId AND ApplicationId = @ApplicationId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_UpdateProduct]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_UpdateProduct]
(
    @ApplicationName	nvarchar(256),
	@ProductId          uniqueidentifier,
	@CategoryId         uniqueidentifier,
	@Title              nvarchar(256),
	@Description        ntext,
	@SKU                nvarchar(50),
	@UnitPrice          money,
	@DiscountPercentage int,
	@UnitsInStock       int,
	@SmallImageUrl      nvarchar(256),
	@FullImageUrl       nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_Products
	   SET CategoryId = @CategoryId,
		  Title = @Title,
		  Description = @Description,
		  SKU = @SKU,
		  UnitPrice = @UnitPrice,
		  DiscountPercentage = @DiscountPercentage,
		  UnitsInStock = @UnitsInStock,
		  SmallImageUrl = @SmallImageUrl,
		  FullImageUrl = @FullImageUrl
	   WHERE ProductId = @ProductId
	   AND ApplicationId = @ApplicationId

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_DecrementUnitsInStock]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_DecrementUnitsInStock]
(
    @ApplicationName	nvarchar(256),
	@ProductId			uniqueidentifier,
	@Quantity			int
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_Products 
	   SET UnitsInStock = UnitsInStock - @Quantity
	   WHERE ProductId = @ProductId  AND ApplicationId = @ApplicationId
END' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_UpdateOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_UpdateOrder]
(
    @ApplicationName	nvarchar(256),
	@OrderId			uniqueidentifier,
	@StatusId			int,
	@ShippedDate		datetime,
	@TransactionId		nvarchar(256),
	@TrackingId			nvarchar(256)
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN 0

	UPDATE xz_Orders
	   SET StatusId = @StatusId,
		  ShippedDate = @ShippedDate,
		  TransactionId = @TransactionId,
		  TrackingID = @TrackingId
	   WHERE OrderId = @OrderId
	   AND ApplicationId = @ApplicationId

END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_DeleteOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_DeleteOrder]
(
    @ApplicationName	nvarchar(256),
	@OrderId			uniqueidentifier
)
AS
BEGIN
    SELECT  @OrderId = o.OrderId
    FROM    [xz_Orders] o, aspnet_Applications a
    WHERE   o.OrderId = @OrderId
        AND o.ApplicationId = a.ApplicationId
        AND LOWER(@ApplicationName) = a.LoweredApplicationName

    IF (@OrderId IS NULL)
        RETURN 0

	DELETE FROM [xz_Orders] WHERE OrderId = @OrderId
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_InsertOrder]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_InsertOrder]
(
    @ApplicationName	nvarchar(256),
    @AddedDate           datetime,
    @AddedBy             nvarchar(256),
    @StatusId            int,
    @ShippingMethod      nvarchar(256),
    @SubTotal            money,
    @Shipping            money,
    @ShippingFirstName   nvarchar(256),
    @ShippingLastName    nvarchar(256),
    @ShippingStreet      nvarchar(256),
    @ShippingPostalCode  nvarchar(50),
    @ShippingCity        nvarchar(256),
    @ShippingState       nvarchar(256),
    @ShippingCountry     nvarchar(256),
    @CustomerEmail       nvarchar(256),
    @CustomerPhone       nvarchar(256),
    @CustomerFax         nvarchar(256),
    @TransactionId       nvarchar(256),
    @OrderId             uniqueidentifier OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL

    EXEC aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT

	SET NOCOUNT ON

    IF( @OrderId IS NULL )
        SELECT @OrderId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT OrderId FROM xz_Orders
                    WHERE OrderId = @OrderId ) )
            RETURN -1
    END
   
	INSERT INTO xz_Orders
	   (ApplicationId, AddedDate, AddedBy, OrderId, StatusId, ShippingMethod, SubTotal, Shipping, ShippingFirstName, ShippingLastName,
		  ShippingStreet, ShippingPostalCode, ShippingCity, ShippingState, ShippingCountry,
		  CustomerEmail, CustomerPhone, CustomerFax, TransactionId)
	   VALUES (@ApplicationId, @AddedDate, @AddedBy, @OrderId, @StatusId, @ShippingMethod, @SubTotal, @Shipping, @ShippingFirstName, @ShippingLastName,
		  @ShippingStreet, @ShippingPostalCode, @ShippingCity, @ShippingState, @ShippingCountry,
		  @CustomerEmail, @CustomerPhone, @CustomerFax, @TransactionId)
END' 
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_GetOrderItems]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_GetOrderItems]
(
    @ApplicationName	nvarchar(256),
	@OrderId			uniqueidentifier
)
AS
SET NOCOUNT ON

SELECT OrderItemId, AddedDate, AddedBy, OrderID, ProductID, Title, SKU, UnitPrice, Quantity
   FROM xz_OrderItems
   WHERE OrderId = @OrderId' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[xz_WebShop_InsertOrderItem]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [xz_WebShop_InsertOrderItem]
(
    @ApplicationName	nvarchar(256),
    @AddedDate     datetime,
    @AddedBy       nvarchar(256),
    @OrderId       uniqueidentifier,
    @ProductId     uniqueidentifier,
    @Title         nvarchar(256),
    @SKU           nvarchar(256),
    @UnitPrice     money,
    @Quantity      int,
    @OrderItemId   uniqueidentifier OUTPUT
)
AS
BEGIN
    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN -1

    BEGIN
        IF( NOT EXISTS( SELECT OrderId FROM xz_Orders
                    WHERE OrderId = @OrderId AND ApplicationId = @ApplicationId ) )
            RETURN -1
    END

    IF( @OrderItemId IS NULL )
        SELECT @OrderItemId = NEWID()
    ELSE
    BEGIN
        IF( EXISTS( SELECT OrderItemId FROM xz_OrderItems
                    WHERE OrderItemId = @OrderItemId ) )
            RETURN -1
    END

	SET NOCOUNT ON
	   
	INSERT INTO xz_OrderItems
	   (OrderItemId, AddedDate, AddedBy, OrderId, ProductId, Title, SKU, UnitPrice, Quantity)
	   VALUES (@OrderItemId, @AddedDate, @AddedBy, @OrderId, @ProductId, @Title, @SKU, @UnitPrice, @Quantity)
END' 
END
GO
